﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using OpenXML.BLL;

namespace OpenXML.DAL
{
    public class ApplicantHelper
    {
        public Applicant getApplicantByID(string id)
        {
            Applicant app = new Applicant();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select * from Applicant Where ApplicantID = '" + id + "';", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    app.ID = reader["ApplicantID"].ToString();
                    app.lastName = reader["ApplicantLastName"].ToString();
                    app.setMiddleName(reader["ApplicantMiddleName"].ToString());
                    app.firstName = reader["ApplicantFirstName"].ToString();
                    app.country = reader["ApplicantCountry"].ToString();
                    app.nationality = reader["ApplicantNationality"].ToString();
                    app.setNationality2(reader["ApplicantNationality2"].ToString());
                    app.setGender(int.Parse(reader["ApplicantGender"].ToString()));
                    app.email = reader["ApplicantEmail"].ToString();
                    app.phoneNumber = reader["ApplicantPhoneNumber"].ToString();
                    app.currentYear = reader["ApplicantCurrentYear"].ToString();
                    app.applyingFor = reader["ApplicantApplyingfor"].ToString();
                }
                reader.Close();
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return app;
        }

        public List<Applicant> getApplicantByName(string lastname, string firstname)
        {
            List<Applicant> list = new List<Applicant>();
            Applicant app = new Applicant();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select * from Applicant Where ApplicantLastName = '" + lastname
                + "' AND ApplicantFirstName = '" + firstname + "';", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    app.ID = reader["ApplicantID"].ToString();
                    app.lastName = reader["ApplicantLastName"].ToString();
                    app.setMiddleName(reader["ApplicantMiddleName"].ToString());
                    app.firstName = reader["ApplicantFirstName"].ToString();
                    app.country = reader["ApplicantCountry"].ToString();
                    app.nationality = reader["ApplicantNationality"].ToString();
                    app.setNationality2(reader["ApplicantNationality2"].ToString());
                    app.setGender(int.Parse(reader["ApplicantGender"].ToString()));
                    app.email = reader["ApplicantEmail"].ToString();
                    app.phoneNumber = reader["ApplicantPhoneNumber"].ToString();
                    app.currentYear = reader["ApplicantCurrentYear"].ToString();
                    app.applyingFor = reader["ApplicantApplyingfor"].ToString();

                    list.Add(app);
                }

                reader.Close();
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }

        public List<Applicant> getListApplicants()
        {
            List<Applicant> list = new List<Applicant>();
            Applicant app = new Applicant();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select * from Applicant;", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    app.ID = reader["ApplicantID"].ToString();
                    app.lastName = reader["ApplicantLastName"].ToString();
                    app.setMiddleName(reader["ApplicantMiddleName"].ToString());
                    app.firstName = reader["ApplicantFirstName"].ToString();
                    app.country = reader["ApplicantCountry"].ToString();
                    app.nationality = reader["ApplicantNationality"].ToString();
                    app.setNationality2(reader["ApplicantNationality2"].ToString());
                    app.setGender(int.Parse(reader["ApplicantGender"].ToString()));
                    app.email = reader["ApplicantEmail"].ToString();
                    app.phoneNumber = reader["ApplicantPhoneNumber"].ToString();
                    app.currentYear = reader["ApplicantCurrentYear"].ToString();
                    app.applyingFor = reader["ApplicantApplyingfor"].ToString();

                    list.Add(app);
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }

        public List<Applicant> getListApplicantsByCountry(string country)
        {
            List<Applicant> list = new List<Applicant>();
            Applicant app = new Applicant();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select * from Applicant Where ApplicantCountry = '" + country + "';", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    app.ID = reader["ApplicantID"].ToString();
                    app.lastName = reader["ApplicantLastName"].ToString();
                    app.setMiddleName(reader["ApplicantMiddleName"].ToString());
                    app.firstName = reader["ApplicantFirstName"].ToString();
                    app.country = reader["ApplicantCountry"].ToString();
                    app.nationality = reader["ApplicantNationality"].ToString();
                    app.setNationality2(reader["ApplicantNationality2"].ToString());
                    app.setGender(int.Parse(reader["ApplicantGender"].ToString()));
                    app.email = reader["ApplicantEmail"].ToString();
                    app.phoneNumber = reader["ApplicantPhoneNumber"].ToString();
                    app.currentYear = reader["ApplicantCurrentYear"].ToString();
                    app.applyingFor = reader["ApplicantApplyingfor"].ToString();

                    list.Add(app);
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }

        public List<Applicant> getListApplicantsByApplicationYear(string year)
        {
            List<Applicant> list = new List<Applicant>();
            Applicant app = new Applicant();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            SqlCommand cmd = new SqlCommand("Select * from Applicant Where ApplicantApplyingfor = '" + year + "';", conn);
            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    app.ID = reader["ApplicantID"].ToString();
                    app.lastName = reader["ApplicantLastName"].ToString();
                    app.setMiddleName(reader["ApplicantMiddleName"].ToString());
                    app.firstName = reader["ApplicantFirstName"].ToString();
                    app.country = reader["ApplicantCountry"].ToString();
                    app.nationality = reader["ApplicantNationality"].ToString();
                    app.setNationality2(reader["ApplicantNationality2"].ToString());
                    app.setGender(int.Parse(reader["ApplicantGender"].ToString()));
                    app.email = reader["ApplicantEmail"].ToString();
                    app.phoneNumber = reader["ApplicantPhoneNumber"].ToString();
                    app.currentYear = reader["ApplicantCurrentYear"].ToString();
                    app.applyingFor = reader["ApplicantApplyingfor"].ToString();

                    list.Add(app);
                }
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }

        public bool insertApplicant(Applicant app)
        {
            bool flag = true;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            string sqlCmd =
                "Insert into Applicant values (@id, @LastName, @MiddleName, @FirstName, "
                + "@Email, @Country, @Nationality, @Nationality2, @PhoneNumber, "
                + "@Gender, @CurrentYear, @Applyingfor);";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlCmd, conn);
                cmd.Parameters.Add("@id", app.ID);
                cmd.Parameters.Add("@LastName", app.lastName);
                cmd.Parameters.Add("@MiddleName", app.getMiddleName());
                cmd.Parameters.Add("@FirstName", app.firstName);
                cmd.Parameters.Add("@Email", app.email);
                cmd.Parameters.Add("@Country", app.country);
                cmd.Parameters.Add("@Nationality", app.nationality);
                cmd.Parameters.Add("@Nationality2", app.getNationality2());
                cmd.Parameters.Add("@PhoneNumber", app.phoneNumber);
                if (app.getGender().Equals("Male"))
                {
                    cmd.Parameters.Add("@Gender", 1);
                }
                else
                {
                    cmd.Parameters.Add("@Gender", 0);
                }
                cmd.Parameters.Add("@CurrentYear", app.currentYear);
                cmd.Parameters.Add("@Applyingfor", app.applyingFor);

                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
                flag = false;
            }
            finally
            {
                conn.Close();
            }

            return flag;
        }

        public bool updateApplicant(Applicant app)
        {
            bool flag = true;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            string sqlCmd =
                "Update Applicant set ApplicantLastName = @LastName, ApplicantMiddleName = @MiddleName, "
            + "ApplicantFirstName = @FirstName, ApplicantEmail = @Email, ApplicantCountry = @Country, "
            + "ApplicantNationality = @Nationality, ApplicantNationality2 = @Nationality2, "
            + "ApplicantPhoneNumber = @PhoneNumber, ApplicantGender = @Gender, ApplicantCurrentYear = @CurrentYear, "
            + "ApplicantApplyingfor = @Applyingfor Where ApplicantID=@id;";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlCmd, conn);
                cmd.Parameters.Add("@id", app.ID);
                cmd.Parameters.Add("@LastName", app.lastName);
                cmd.Parameters.Add("@MiddleName", app.getMiddleName());
                cmd.Parameters.Add("@FirstName", app.firstName);
                cmd.Parameters.Add("@Email", app.email);
                cmd.Parameters.Add("@Country", app.country);
                cmd.Parameters.Add("@Nationality", app.nationality);
                cmd.Parameters.Add("@Nationality2", app.getNationality2());
                cmd.Parameters.Add("@PhoneNumber", app.phoneNumber);
                if (app.getGender().Equals("Male"))
                {
                    cmd.Parameters.Add("@Gender", 1);
                }
                else
                {
                    cmd.Parameters.Add("@Gender", 0);
                }
                cmd.Parameters.Add("@CurrentYear", app.currentYear);
                cmd.Parameters.Add("@Applyingfor", app.applyingFor);

                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
                flag = false;
            }
            finally
            {
                conn.Close();
            }

            return flag;
        }

        public bool deleteApplicant(Applicant app)
        {
            bool flag = true;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            string sqlCmd =
                "Delete from Applicant Where ApplicantID=@id;";

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlCmd, conn);
                cmd.Parameters.Add("@id", app.ID);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                Console.Write(err.Message);
                flag = false;
            }
            finally
            {
                conn.Close();
            }

            return flag;
        }
    }
}